package com.itlaobing.dataaccess.jdbc;

import com.itlaobing.dataaccess.dao.EmployeeDao;
import com.itlaobing.dataaccess.entity.Employee;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.List;

/**
 * 1、手动注入 或 自动装配 JdbcTemplate
 * 2、public int update( String sql , Object... args ) 可以执行 DML语句 ( UPDATE / DELETE / INSERT )
 * 3、public <T> T queryForObject( String sql , RowMapper<T> rowMapper , Object... args )
 *      可以执行查询语句并根据 rowMapper 将结果集中的每行数据封装成T类型的对象
 * 4、public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args)
 *      可以执行查询语句并根据 rowMapper 将结果集中的每行数据封装成T类型的对象组成的List集合
 * 5、public <T> T queryForObject( String sql , Class<T> requiredType ) 用于执行SQL语句并返回T类型的对象
 */
public class EmployeeDaoImplByJdbc implements EmployeeDao {

    private JdbcTemplate jdbcTemplate;

    @Override
    public int save(Employee employee) {
        String sql = "INSERT INTO t_employees ( name , gender , hire_date , married , id ) VALUES ( ? , ? , ? , ? , ? )" ;

        String gender = String.valueOf(employee.getGender());
        LocalDate localDate = employee.getHireDate();
        java.sql.Date hireDate = null;
        if (localDate != null){
            hireDate = java.sql.Date.valueOf(localDate);
        }
        String married = employee.isMarried() ? "Y" : "N" ;
        Object[] args = {employee.getName() , gender , hireDate , married , employee.getId() };

        return jdbcTemplate.update(sql , args);
    }

    @Override
    public int remove(Integer id) {
        String sql = " ";
        Object[] args = { };
        return jdbcTemplate.update(sql , args);
    }

    @Override
    public int update(Employee employee) {
        String sql = " ";
        Object[] args = { };
        return jdbcTemplate.update(sql , args);
    }

    @Override
    public Employee find(Integer id) {
        String sql = "SELECT name , gender , hire_date , married , id FROM t_employees WHERE id = ? " ;

        RowMapper<Employee> rowMapper = new RowMapper<Employee>() {
            @Override
            public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));

                java.sql.Date date = rs.getDate("hire_date");
                LocalDate hireDate = null;
                if (date!=null){
                    hireDate = date.toLocalDate();
                    emp.setHireDate(hireDate);
                }

                String genderString = rs.getString("gender");
                if( genderString != null && genderString.length() > 0 ) {
                    char gender = genderString.charAt(0);
                    emp.setGender(gender);
                }

                String marriedString = rs.getString("married");
                if( marriedString != null && marriedString.length() > 0 ) {
                    marriedString = marriedString.toUpperCase();
                    char marriedChar = marriedString.charAt(0);
                    boolean married = ( marriedChar == 'Y' || marriedChar == 'T' ) ? true : false ;
                }

                return emp;
            }
        };
        Employee e = jdbcTemplate.queryForObject(sql , rowMapper , id);
        return e;
    }

    @Override
    public List<Employee> findAll() {
        String sql = "" ;
        RowMapper<Employee> rowMapper = null ;
        // query( String sql , RowMapper<T> rowMapper )
        List<Employee> employeeList = jdbcTemplate.query( sql , rowMapper );
        return employeeList;
    }

    @Override
    public List<Employee> list(String name) {
        String sql = "" ;
        RowMapper<Employee> rowMapper = null ;
        // query( String sql , RowMapper<T> rowMapper , Object... args )
        List<Employee> employeeList = jdbcTemplate.query( sql , rowMapper , name );
        return employeeList;
    }

    @Override
    public Integer nextId() {
        String sql = "SELECT NVL( max(id) , 0 ) + 1 FROM t_employees" ; // ORACLE
        Integer id = jdbcTemplate.queryForObject( sql , Integer.class );
        return id ;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
